package util.jdbc;

import java.io.IOException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

/**
 * 
 * 数据库操作工具类
 * 
 * */
public class DBUtils {
	private static DataSource dataSource;//数据源
	static {
		try {
			Properties properties = new Properties();
			properties.load(DBUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"));
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		} catch (IOException e) {
			throw new RuntimeException("配置文件不存在");
		} catch (Exception e) {
			throw new RuntimeException("建立数据源异常");
		}
	}
	
	/*
	 * 连接
	 * */
	public static Connection getConnection(){
		Connection con=null;
		try {
			con=dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException("连接错误");
		}
		return con;
	}
	
	/*
	 * 创建SQL语句执行对象
	 * @param cmd:SQL执行语句
	 * */
	public static Statement createStatement(Connection con,String cmd){
		try {
			if(con==null || con.isClosed()){
				con=getConnection();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			return con.createStatement();
		} catch (SQLException e) {
			System.out.println("[sql错误]在关闭的连接上调用此方法...\n"+e.getMessage());
			return null;
		}
	}
	
	/*
	 * 创建参数格式化对象
	 * @param  cmd:SQL执行语句   
	 * @param param  此参数可选,用于参数格式化
	 * @return 一个格式化好点的SQL执行对象
	 * */
	public static PreparedStatement prepareStatement(Connection con,String sql,Object[] param){
		try {
			if(con==null || con.isClosed()){
				con=getConnection();
			}
			//PreparedStatement state=con.prepareCall(sql);
			PreparedStatement state = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//
			for(int i=0;i<param.length;i++){
				state.setObject(i+1, param[i]);
			}
			return state;
		} catch (SQLException e) {
			System.out.println("[sql错误]在关闭的连接上调用此方法.....\n"+e.getMessage());
			return null;
		}
	}

	
	/*
	 * 一系列释放资源
	 * */
	public static void closeConnection(Connection con){
		try {
			if(con!=null && !con.isClosed()){				
				con.close();
				con=null;
			}
		} catch (SQLException e) {
			System.out.println("[sql错误]关闭异常.....\n"+e.getMessage());
			con=null;
		}
	}	
	public static void closeStatement(Statement state){
		try {
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void closePrepareStatement(PreparedStatement ps){
		try {
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/*
	 * 
	 * 更新：更删改操作
	 * @param cmd sql语句
	 * @param param  此参数可选,用于参数格式化
	 * @return 返回受影响的行数
	 * 此方法用于更新、删除、修改操作，如果有二个参数则进行格式化参数
	 * */
	public static int execute(String sql,Object...param){
		Statement state=null;
		Connection con=getConnection();
		int count=0;
		try {
			if(param.length==0){
				state=DBUtils.createStatement(con,sql);
				count=state.executeUpdate(sql);
			}else{
				state=DBUtils.prepareStatement(con,sql,param);
				count=((PreparedStatement)state).executeUpdate();
			}
			return count;
		} catch (SQLException e) {
			System.out.println("[sql错误]在已关闭的 Statement 上调用此方法，或者返回的一个Result对象....\n"+e.getMessage());
			return -1;
		}finally{
			try{
				if(state!=null){
					state.close();
					state=null;
				}
			}catch(Exception ex){
				System.out.println("[sql错误]关闭不了...\n"+ex.getMessage());
				state=null;
				return -1;
			}
			closeConnection(con);
		}
	}



/*	
	 * 第一种：查找
	 * @return 返回结果集
	 * 
	public static ResultSet query(String sql,Object...param){
		Statement state=null;
		ResultSet rs=null;
		try {
			if(param.length==0){
				state=DBUtils.createStatement(sql);
				rs=state.executeQuery(sql);
			}else{
				state=DBUtils.prepareStatement(sql,param);
				rs=((PreparedStatement)state).executeQuery();
			}
			return rs;
		} catch (SQLException e) {
			System.out.println("[sql错误]在已关闭的 Statement 上调用此方法，或者返回的一个Result对象....\n"+e.getMessage());
			return null;
		}finally{
			try{
				if(state!=null){
					state.close();
					state=null;
				}
			}catch(Exception ex){
				System.out.println("[sql错误]关闭不了...\n"+ex.getMessage());
			}
			close();
		}
	}*/
	/**
	 * 第一种查询封装
	 * 
	 * @param sql
	 * @return 返回格式为 元素,元素,元素; ,号隔开每一个列元素 ;号代表一行数据
	 * 
	 */
	public static ArrayList<String> query(String sql,Object...param) {
		Statement state=null;
		ArrayList<String> list = new ArrayList<String>();
		ResultSet rs=null;
		Connection con=getConnection();
		try {
			if(param.length==0){
				state=DBUtils.createStatement(con,sql);
				rs=state.executeQuery(sql);
			}else{
				state=DBUtils.prepareStatement(con,sql,param);
				rs=((PreparedStatement)state).executeQuery();
			}
			ResultSetMetaData rsmd = rs.getMetaData();
			String str = "";
			while (rs.next()) {
				// rsmd.getColumnCount 获取当前行的列数
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					str += rs.getObject(i) + ",";
				}
				// 删除最后一个,号
				str = str.substring(0, str.lastIndexOf(","));
				list.add(str);
				str = "";
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				state.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		closeConnection(con);
		return list;
	}
	

	/*
	 * 第二种:查询
	 * 反射封装:调用此方法，必须有数据库层的相对应javaBean
	 * 
	 * @param cmd 为 sql语句
	 * @param  T  为javaBean的对象
	 * @param param  此参数可选,用于参数格式化
	 * @return 传入对象 返回对象集合
	 * */
	public static <T> List<T> packageQuery(String sql,Class<T> beanClass,Object...param){
		Statement state=null;
		ResultSet rs=null;
		List<T> list=new ArrayList<T>();
		//Class<? extends Object> c=t.getClass();
		Connection con=getConnection();
		try {
			if(param.length==0){
				state=DBUtils.createStatement(con,sql);
				rs= state.executeQuery(sql);
			}else{
				state=DBUtils.prepareStatement(con,sql,param);
				rs=((PreparedStatement)state).executeQuery();
			}
			while(rs.next()){
				T obj=null;
				try {
					obj = beanClass.newInstance();
				} catch (Exception e) {
					System.out.println("[sql错误]javaBean对象创建错误"+e.getMessage());
					return null;
				}
				//要保存的对象
				ResultSetMetaData rsmd=rs.getMetaData();
				for(int i=1;i<=rsmd.getColumnCount();i++){//每一行的列
					Method[] methods=beanClass.getMethods();
					for (int j = 0; j < methods.length; j++){//对象所有的方法 
						if(methods[j].getName().equalsIgnoreCase("set"+rsmd.getColumnName(i))){//判断数据的列名与对象的方法
							//判断列类型是否为int
							String type=rsmd.getColumnTypeName(i).split(" ")[0];
//System.out.println("类型："+type);
							if(type.matches("(?i)int|TINYINT|mediumint|smallint")){
								methods[j].invoke(obj, rs.getInt(i));
							}else if(type.matches("(?i)BIGINT")){//判断为long
								methods[j].invoke(obj, rs.getLong(i));
							}else if(type.matches("(?i)float|money")){//判断为float
								methods[j].invoke(obj, rs.getFloat(i));
							}else if(type.matches("(?i)double")){//判断为double
								methods[j].invoke(obj, rs.getDouble(i));
							}else if(type.matches("(?i)boolean")){//判断为Boolean
								methods[j].invoke(obj, rs.getBoolean(i));
							}else if(type.matches("(?i)char|varchar|nchar|nvarchar|text|date|datetime")){//判断为字符串
								methods[j].invoke(obj, rs.getString(i));
							}
						}
					}
				}
				list.add(obj);
			}
			return list;
		} catch (SQLException e) {
			System.out.println("[sql错误].....\n"+e.getMessage());
			return null;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}finally{
			try{
				if(state!=null){
					state.close();
					state=null;
				}
			}catch(Exception ex){
				System.out.println("[sql错误].....\n"+ex.getMessage());
				state=null;
			}
			closeConnection(con);
		}
	}
}




